﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using CP.Entities;
using CP.DBService;
using CP.Utility;

namespace CP.Repositories.GPM
{
    public class UserInfoRepository
    {
        DBHelper db = DBHelper.CreateInstance();

        public IList<User_Info> GetAllUsers()
        {
            return db.ExecuteObject<User_Info>("select * from user_info order by user_id");
        }

        public IList<User_Info> GetUsersByGroupID(int groupid)
        {
            return db.ExecuteObject<User_Info>(string.Format(@"select t1.* from 
                            user_info t1,USER_GROUP_RELATION t2 
                            where t1.user_id = t2.user_id and
                                  t2.group_id = {0} order by t1.user_id", groupid));
        }

        public User_Info GetUserByPwd(string userid, string pwd)
        {
            IList<User_Info> users = db.ExecuteObject<User_Info>(string.Format(@"select * from user_info
                                            where user_id = '{0}' 
                                              and password = '{1}' ",
                                                  userid, pwd));
            if (users.Count > 0)
                return users[0];
            return null;
        }

        public IList<User_Info> GetUsersByMenuID(int menuid)
        {
            return db.ExecuteObject<User_Info>(string.Format(@"SELECT t3.*
                          FROM user_info t3,
                               (SELECT DISTINCT t2.user_id
                                           FROM menu_right t1, user_group_relation t2
                                          WHERE t1.GROUP_ID = t2.GROUP_ID 
                                                AND t1.menu_id = {0}) t4
                         WHERE t3.user_id = t4.user_id order by t3.user_id", menuid));

        }

        public IList<User_Info> GetUsersByButtonID(int webpartid, int buttonid)
        {
            return db.ExecuteObject<User_Info>(string.Format(@"SELECT t3.*
                          FROM user_info t3,
                               (SELECT DISTINCT t2.user_id
                                FROM webpart_right_button t1, user_group_relation t2
                                WHERE t1.GROUP_ID = t2.GROUP_ID 
                                    AND t1.webpart_id = {0}
                                    AND t1.button_id = {1} ) t4
                         WHERE t3.user_id = t4.user_id order by t3.user_id",
                                                         webpartid, buttonid));

        }

        public User_Info GetUserByID(string userid)
        {
            return db.ExecuteSingleObject<User_Info>(
                        string.Format(@"select * from user_info 
                         where user_id = '{0}' ", userid));
        }

        public IList<User_Group_Relation> GetUserRoleRelations(string userid)
        {
            return db.ExecuteObject<User_Group_Relation>(
                string.Format(@"select * from user_group_relation 
                                where user_id = '{0}' ", userid));
        }

        public IList<User_Group_Relation> GetUserRoleRelations(int groupid)
        {
            return db.ExecuteObject<User_Group_Relation>(
                string.Format(@"select * from user_group_relation 
                                where group_id = {0} ", groupid));
        }

        public void DeleteUserInfo(IList<string> userids)
        {
            try
            {
                db.BeginTransaction();
                foreach (string userid in userids)
                {
                    db.ExecuteNonQuery(string.Format("delete user_info where user_id = '{0}'", userid));
                }
                db.Commit();
            }
            catch (Exception ex)
            {
                db.Rollback();
                throw ex;
            }
        }

        public void AddUser(User_Info user)
        {
            try
            {
                //db.BeginTransaction();
                db.ExecuteNonQuery(string.Format(@"insert into user_info(USER_ID,
                                DEPT_ID,GROUP_ID,USER_NAME,PASSWORD,REMARK,is_enabled) 
                                values('{0}',{1},{2},'{3}','{4}','{5}','{6}')",
                                         user.user_id, user.dept_id, user.group_id,
                                         user.user_name, user.password,
                                         user.remark, user.is_enabled));
//                db.ExecuteNonQuery(string.Format(@"delete user_group_relation  
//                                    where user_id ='{0}' and group_id ={1}",
//                                         user.user_id, user.group_id));
//                db.ExecuteNonQuery(string.Format(@"insert into user_group_relation  
//                                    (user_id,group_id) values('{0}',{1}) ",
//                                         user.user_id, user.group_id));
                //db.Commit();
            }
            catch (Exception ex)
            {
                //db.Rollback();
                throw ex;
            }
        }

        public void UpdateUser(User_Info user)
        {
            try
            {
                //db.BeginTransaction();

                User_Info old = GetUserByID(user.user_id);

                db.ExecuteNonQuery(string.Format(@"update user_info set 
                                DEPT_ID = {1},GROUP_ID={2},USER_NAME='{3}',
                                PASSWORD='{4}',REMARK='{5}',is_enabled = '{6}' where USER_ID = '{0}'",
                                    user.user_id, user.dept_id, user.group_id,
                                    user.user_name, user.password,
                                    user.remark, user.is_enabled));
//                if (old.group_id != user.group_id)
//                {
//                    db.ExecuteNonQuery(string.Format(@"delete user_group_relation  
//                                    where user_id ='{0}' and group_id ={1}",
//                                             user.user_id, old.group_id));
//                    db.ExecuteNonQuery(string.Format(@"insert into user_group_relation  
//                                    (user_id,group_id) values('{0}',{1}) ",
//                                             user.user_id, user.group_id));
//                }
                //db.Commit();
            }
            catch (Exception ex)
            {
                //db.Rollback();
                throw ex;
            }

        }

        public void ChangePassword(string userid, string newpassword)
        {
            db.ExecuteNonQuery(string.Format(@"update user_info set 
                                        password = '{1}' where user_id = '{0}'", userid, 
                                                    newpassword));
        }
    }
}
